Skip to main content

PostgreSQL Database Sample

This sample integrates with the Identity Platform to authenticate users to the application and connects to a Cloud SQL postgreSQL database for data storage.

Use it with the End user Authentication for Cloud Run.

For more details on how to work with this sample read the Google Cloud Run Node.js Samples README.

Run on Google Cloud

Dependencies​

  • express: Web server framework
  • winston: Logging library
  • firebase-admin: Verifying JWT token
  • knex + pg: A postgreSQL query builder library
  • handlebars.js: Template engine
  • google-auth-library-nodejs: Access compute metadata server for project ID
  • Firebase JavaScript SDK: client-side library for authentication flow

Environment Variables​

Cloud Run services can be configured with Environment Variables. Required variables for this sample include:

  • CLOUD_SQL_CREDENTIALS_SECRET: the resource ID of the secret, in format: projects/PROJECT_ID/secrets/SECRET_ID/versions/VERSION when deployed to Cloud Run. At runtime, Cloud Run will inject the secret value as an environment variable, for more info see Using secrets. See postgres-secrets.json for secret content.

OR

  • CLOUD_SQL_CONNECTION_NAME: Cloud SQL instance name, in format: <MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>
  • DB_NAME: Cloud SQL postgreSQL database name
  • DB_USER: database user
  • DB_PASSWORD: database password

Other environment variables:

  • Set TABLE to change the postgreSQL database table name.

  • Set DB_HOST to use the proxy with TCP. See instructions below.

  • Set DB_SOCKET_PATH to change the directory when using the proxy with Unix sockets. See instructions below.

Production Considerations​

  • Both postgres-secrets.json and static/config.js should not be committed to a git repository and should be added to .gitignore.

  • Saving credentials directly as environment variables is convenient for local testing, but not secure for production; therefore using CLOUD_SQL_CREDENTIALS_SECRET in combination with the Cloud Secrets Manager is recommended.

Running Locally​

  1. Set environment variables.

  2. To run this application locally, download and install the cloud_sql_proxy by following the instructions.

The proxy can be used with a TCP connection or a Unix Domain Socket. On Linux or Mac OS you can use either option, but on Windows the proxy currently requires a TCP connection.

Testing​

Tests expect the Cloud SQL instance to already be created and environment Variables to be set.

Unit tests​

npm run test

System tests​

export GOOGLE_CLOUD_PROJECT=<YOUR_PROJECT_ID>
export CLOUD_SQL_CONNECTION_NAME=<YOUR_CLOUD_SQL_CONNECTION_NAME>
export DB_PASSWORD=<POSTGRESQL_PASSWORD>
export IDP_KEY=<IDENTITY_PLATFORM_API_KEY>  # See tutorial for creation of this key
npm run system-test